package com.ht.dao;

import com.ht.bean.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

public interface NoticeDao {

    //员工公告信息
    @Select("select * from notice where noticeType = 1 order BY noticeTime DESC limit 0,4;")
    List<Notice> selectNoticeMsg();

    //学生公告信息
    @Select("select * from notice where noticeType = 2 order BY noticeTime DESC limit 0,4;")
    List<Notice> selectNoticeMsgs();

    @Select({"<script>",
            "select n.*,e.empName," +
                    "(select count(*) from noticereceiver nr where n.noticeId=nr.noticeId and nr.isRead=1) as readCnt," +
                    "(select count(*) from noticereceiver nr where n.noticeId=nr.noticeId and nr.isRead=2) as unReadCnt from notice n, emp e",
            "<where>",
            "n.empId = e.empId",
            "<if test=\"notice.noticeId!=null and notice.noticeId!=''\">",
            "and n.noticeId = #{notice.noticeId}",
            "</if>",
            "<if test=\"emp.empName!=null and emp.empName!=''\">",
            "and empName like concat('%',#{emp.empName},'%')",
            "</if>",
            "</where>",
            "limit #{startIndex},#{pageSize}",
            "</script>"})
    List<Map> selectNoandEmp(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize, @Param("emp") Emp emp, @Param("notice") Notice notice);

    @Select({"<script>",
            " select count(*) from notice n,emp e",
            "<where>",
            "n.empId = e.empId",
            "<if test=\"notice.noticeId!=null and notice.noticeId!=''\">",
            "and n.noticeId = #{notice.noticeId}",
            "</if>",
            "<if test=\"emp.empName!=null and emp.empName!=''\">",
            "and empName like concat('%',#{emp.empName},'%')",
            "</if>",
            "</where>",
            "</script>"})
    int countNoandEmp(@Param("emp") Emp emp, @Param("notice") Notice notice);

    @Delete("delete from notice where noticeId = #{noticeId}")
    boolean deleteNoticeId(Integer noticeId);

    @Delete("delete from noticereceiver where noticeId = #{noticeId}")
    boolean deleteNoticereceiverId(Integer noticeId);

    @Insert("insert into notice values (null,#{title},#{noticeType},#{content},#{empId},NOW(),#{clazz},#{empIds})")
    @Options(useGeneratedKeys = true, keyProperty = "noticeId", keyColumn = "noticeId")
    boolean insertNoticeMsg(Notice notice);

    @Select("select n.*,e.empName from emp e,notice n where e.empId = n.empId and n.noticeId = #{noticeId} LIMIT 1")
    List<Map> selectNoticeByNoticeId(Integer noticeId);

    //查询公告表信息
    @Select("select * from notice where noticeId = #{noticeId}")
    Notice selectNoticeById(Integer noticeId);

    @Update("UPDATE notice set noticeId = #{noticeId},title = #{title},noticeType = #{noticeType}" +
            ",content = #{content},empId = #{empId},noticeTime = #{noticeTime},clazz = #{clazz}" +
            ",empIds = #{empIds}  where noticeId = #{noticeId}")
    boolean updateNoticeId(Notice notice);

    //查询班级表
    @Select("select * from studentclass")
    public List<Studentclass> studentclassList();

    //列出所有员工的Id
    @Select("select empId from emp")
    Integer[] selectAllEmpId();

    //列出所有学生ID
    @Select("select stuId from student")
    Integer[] selectAllStuId();

    //列出某个班级的学生Id
    @Select("select stuId from student where clazz = #{clazz}")
    Integer[] selectAllClaStuId(Notice notice);

    @Insert("insert into noticereceiver values(null,#{noticeId},#{receiver},#{isRead},#{isStudent})")
    boolean insertNoticeReceivce(Noticereceiver noticereceiver);

    @Insert("insert into noticereceiver values(null,#{noticeId},#{receiver},#{isRead},#{isStudent})")
    boolean insertNoticeReceivces(Noticereceiver noticereceiver);

    @Update("update noticereceiver set isRead = 1 " +
            "where noticeId = #{noticeId} and receiver = #{receiver} and isStudent = 1")
    boolean updnoticereceiver(Noticereceiver noticereceiver);


    @Update("update noticereceiver set isRead = 1 " +
            "where noticeId = #{noticeId} and receiver = #{receiver} and isStudent = 2")
    boolean updnoticereceivers(Noticereceiver noticereceiver);

    @Update("update noticereceiver set isRead = 1 " +
            "where noticeId = #{noticeId} and receiver = #{receiver} and isStudent = 3")
    boolean updnoticereceiveres(Noticereceiver noticereceiver);

    @Select("select  n.title ,n.noticeTime,e.empName,nc.isRead from notice n ,noticereceiver nc,emp e " +
            "where n.noticeId = #{notice.noticeId} and nc.noticeId = #{notice.noticeId} " +
            "and nc.isRead = 2 and nc.isStudent = 1 and nc.receiver = e.empId")
    List<Map> allsynotice(@Param("notice") Notice notice, @Param("isStudent") Integer isStudent);

    @Select("select  n.title ,n.noticeTime,s.stuname,r.isRead from notice n ,noticereceiver r,student s,studentclass sc " +
            "where n.noticeId = #{notice.noticeId} and r.noticeId = #{notice.noticeId} and r.isRead = 2 and r.isStudent = 2 and " +
            "r.receiver = s.stuId and s.clazz = sc.classId")
    List<Map> allsynotices(@Param("notice") Notice notice, @Param("isStudent") Integer isStudent);

    @Select("select  n.title ,n.noticeTime,s.stuname,nc.isRead from notice n,noticereceiver nc,student s,studentclass sc where n.noticeId = #{notice.noticeId} and nc.noticeId = #{notice.noticeId} and s.clazz = sc.classId and n.classIds = s.clazz  and nc.isRead = 2 and nc.isStudent = 3 and s.stuId = nc.receiver")
    List<Map> allsynoticeies(@Param("notice") Notice notice, @Param("isStudent") Integer isStudent);

    @Select("select count(*) from notice")
    int countNotice();

    @Select("select * from noticereceiver where noticeId = #{noticeId}")
    Noticereceiver selectNoc(Integer noticeId);

}
